package org.compiere.report;

import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MOrg;
import org.compiere.model.X_A_Asset;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;

public class A_Asset_Form_OS2 extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;

	int p_A_Asset_ID;
	int p_A_IntMove_ID;
	int p_C_BPartner_ID;
	
	String getSQlresult;
	String file;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("A_Asset_ID")) 
				p_A_Asset_ID  = para[i].getParameterAsInt();
			else if (name.equals("A_InternalMovement_ID")) 
				p_A_IntMove_ID  = para[i].getParameterAsInt();
			else if (name.equals("C_BPartner_ID")) 
				p_C_BPartner_ID  = para[i].getParameterAsInt();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		// / here another code)
	}

	/**
     * Create a library of cell styles
     */
    private static Map<String, CellStyle> createStyles(Workbook wb){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style;
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short)11);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(titleFont);
        styles.put("title", style);
        return styles;
    }
	
	
	@Override
	protected String doIt() throws Exception {

		file = Util.localFilePath + "Form_OS2.xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		Workbook wb = new HSSFWorkbook(entry.getInputStream());
		Sheet sheet = wb.getSheetAt(0);
		Map<String, CellStyle> s = createStyles(wb);
		Cell c = null;

		
		// current date
		Timestamp currdate = new Timestamp(System.currentTimeMillis());
		SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
		String str = dateFormat.format(currdate);
		Row R = sheet.getRow(18); c = R.getCell(76); c.setCellValue(str); 
		
		X_A_Asset asset = new X_A_Asset (getCtx(), p_A_Asset_ID, get_TrxName());
		
		// BP name and TIN
		MOrg org = MOrg.get(m_ctx,asset.getAD_Org_ID());
		MBPartner bpartner = MBPartner.get(m_ctx, org.getLinkedC_BPartner_ID(get_TrxName()));
		
		R = sheet.getRow(10);
        c = R.getCell(7); c.setCellValue(org.getName()); c.setCellStyle(s.get("title")); 
        c = R.getCell(64); c.setCellValue(bpartner.get_Value("FI_TIN").toString()); 
        
		int iBP1_ID=0,iBP2_ID=0, im_ID=0, iBPSR_ID=0;
		Timestamp DateMoved=null;
		Timestamp DateOrder=null;
		String sDescription=null;
		String sDocNo=null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT A_InternalMovement_ID, DateMoved, Description "
				+ ", C_BPartnerSR_ID, DocumentNo "
				+ "FROM A_InternalMovement "
				+ "WHERE AD_Client_ID=" + Env.getAD_Client_ID(m_ctx)
				+ " AND A_InternalMovement_ID="+p_A_IntMove_ID
				+ " AND A_Asset_ID="+p_A_Asset_ID
				+ " AND IsActive = 'Y'";		
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				im_ID = rs.getInt("A_InternalMovement_ID");
				DateMoved = rs.getTimestamp("DateMoved");
				sDescription = rs.getString("Description");
				iBPSR_ID = rs.getInt("C_BPartnerSR_ID");
				sDocNo = rs.getString("DocumentNo");
			}
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally
		{
			DB.close(rs, pstmt);
		}
		if (DateMoved==null) log.log(Level.SEVERE, sql.toString(), "DateMoved is NULL!"); 

		int i=1;
		BigDecimal bTotal=Env.ZERO;
		
		sql = "SELECT A_Asset_ID "
				+ "FROM A_InternalMovement "
				+ "WHERE AD_Client_ID=" + Env.getAD_Client_ID(m_ctx)
				+ " AND DateMoved='"+DateMoved+"'"
				+ " AND DocumentNo='"+sDocNo+"'"
				+ " AND C_BPartnerSR_ID="+iBPSR_ID
				+ " AND IsActive = 'Y'";		
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				asset = new X_A_Asset (getCtx(), rs.getInt("A_Asset_ID"), get_TrxName());
				R = sheet.getRow(i+24);
				c = R.getCell(0); c.setCellValue(i); 
				c = R.getCell(3); c.setCellValue(asset.getName()); // Name 
				c = R.getCell(49); c.setCellValue(asset.getValue()); // Value
				c = R.getCell(61); c.setCellValue(asset.getSerNo()); // SerNo
		        // Service Date
				if (asset.getAssetServiceDate()!=null) {
					str = dateFormat.format(asset.getAssetServiceDate());
					c = R.getCell(39); c.setCellValue(str); 
				}
				BigDecimal bCost = asset.getAssetCost(DateMoved);
				// Cost
				if (bCost!=null) { 
					c = R.getCell(73); c.setCellValue(bCost.doubleValue());
					bTotal=bTotal.add(bCost);
				}
				
				i++;
			}
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally
		{
			DB.close(rs, pstmt);
		}
		
		
		
		// DateMoved
		iBP2_ID = asset.getC_BPartnerSR_ID(DateMoved);
		Calendar cal = Calendar.getInstance();
		cal.setTime(DateMoved);
		cal.add(Calendar.DAY_OF_WEEK, -1);
		DateMoved.setTime(cal.getTime().getTime()); 
		iBP1_ID = asset.getC_BPartnerSR_ID(DateMoved);

		if (sDocNo!=null) {
		// DocNo
			R = sheet.getRow(18);
			c = R.getCell(68); c.setCellValue(sDocNo);  
			R = sheet.getRow(20);
			c = R.getCell(9); c.setCellValue(sDocNo);  
			sql = "SELECT DateFrom FROM HRM_Order" 
					  +" WHERE HRM_OrderType = '1'"
					  +" AND HRM_Number = '"+sDocNo+"'"
					  +" AND HRM_Order_ID IN" 
						+" (  SELECT HRM_Order_ID FROM HRM_Employee"
							+" WHERE C_BPartner_ID = "+iBP2_ID+")";
			try
			{
				pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
				rs = pstmt.executeQuery();
				while (rs.next())
				{
					DateOrder = rs.getTimestamp("DateFrom");
				}
			}
			catch (Exception e)
			{
				log.log(Level.SEVERE, sql.toString(), e);
			}
			finally
			{
				DB.close(rs, pstmt);
			}
		}
		if(DateOrder == null)
			DateOrder = new Timestamp(System.currentTimeMillis());
		str = dateFormat.format(DateOrder);
		R = sheet.getRow(20); c = R.getCell(30); c.setCellValue(str);  
		
		
		
		
		if (sDescription!=null) {
		// Description
			R = sheet.getRow(19);
			c = R.getCell(19); c.setCellValue(sDescription);  
		}

		// Total
		R = sheet.getRow(35);
		c = R.getCell(73); c.setCellValue(bTotal.doubleValue());  
		
		String sJobName1 = "";
		String sJobName2 = "";
		String sDepartment1 = "";
		String sDepartment2 = "";
		
		sql = "SELECT Name FROM HR_Job WHERE HR_Job_ID = ("
			    +" SELECT HR_Job_ID FROM HR_Employee "
				+" WHERE C_BPartner_ID = "+iBP1_ID
				+ "  AND StartDate <= '"+DateMoved+"'"
				+ "  AND EndDate >= '"+DateMoved+"'" 
				+ "  AND AD_Client_ID=" + Env.getAD_Client_ID(m_ctx)
				+ " AND IsActive = 'Y'"		
				+" )";
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				sJobName1=rs.getString("Name");
			}
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally
		{
			DB.close(rs, pstmt);
		}
		
		sql = "SELECT Name FROM HR_Job WHERE HR_Job_ID = ("
			    +" SELECT HR_Job_ID FROM HR_Employee "
				+" WHERE C_BPartner_ID = "+iBP2_ID
				+ "  AND StartDate <= '"+DateMoved+"'"
				+ "  AND EndDate >= '"+DateMoved+"'" 
				+ "  AND AD_Client_ID=" + Env.getAD_Client_ID(m_ctx)
				+ " AND IsActive = 'Y'"		
				+" )";
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				sJobName2=rs.getString("Name");
			}
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally
		{
			DB.close(rs, pstmt);
		}

		sql = "SELECT Name FROM HR_Department WHERE HR_Department_ID = ("
			    +" SELECT HR_Department_ID FROM HR_Employee "
				+" WHERE C_BPartner_ID = "+iBP1_ID
				+ "  AND StartDate <= '"+DateMoved+"'"
				+ "  AND EndDate >= '"+DateMoved+"'" 
				+ "  AND AD_Client_ID=" + Env.getAD_Client_ID(m_ctx)
				+ " AND IsActive = 'Y'"		
				+" )";
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				sDepartment1=rs.getString("Name");
			}
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally
		{
			DB.close(rs, pstmt);
		}
		
		sql = "SELECT Name FROM HR_Department WHERE HR_Department_ID = ("
			    +" SELECT HR_Department_ID FROM HR_Employee "
				+" WHERE C_BPartner_ID = "+iBP2_ID
				+ "  AND StartDate <= '"+DateMoved+"'"
				+ "  AND EndDate >= '"+DateMoved+"'" 
				+ "  AND AD_Client_ID=" + Env.getAD_Client_ID(m_ctx)
				+ " AND IsActive = 'Y'"		
				+" )";
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				sDepartment2=rs.getString("Name");
			}
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally
		{
			DB.close(rs, pstmt);
		}
		
		if (iBP1_ID>0) { 
			R = sheet.getRow(11); c = R.getCell(5); c.setCellValue(sDepartment1); 
			MBPartner bp1 = MBPartner.get(m_ctx, iBP1_ID);
			R = sheet.getRow(50); c = R.getCell(4); c.setCellValue(sJobName1); 
			R = sheet.getRow(50); c = R.getCell(40); c.setCellValue(bp1.getName()); 
		}
		if (iBP2_ID>0) { 
			R = sheet.getRow(13); c = R.getCell(6); c.setCellValue(sDepartment2); 
			MBPartner bp2 = MBPartner.get(m_ctx, iBP2_ID);
			R = sheet.getRow(53); c = R.getCell(4); c.setCellValue(sJobName2); 
			R = sheet.getRow(53); c = R.getCell(40); c.setCellValue(bp2.getName()); 
		}
		
		if (p_C_BPartner_ID>0) {
			   MBPartner bp3 = MBPartner.get(m_ctx, p_C_BPartner_ID);
				R = sheet.getRow(59); c = R.getCell(30);  c.setCellValue(bp3.getName()); 
		}
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(file);
		wb.write(fileOut);
		fileOut.close();

		try {
			//Forces Excel to use the specified file as a template for the default workbook. 
			Runtime.getRuntime().exec("cmd /c start excel " + file+" /t");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return Msg.translate(m_ctx, "Success");
	}
}